package com.dyh.quickdemozwei.hxfz;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class TestExcel {
    private static final String DB_URL = "jdbc:your_database_url";
    private static final String USER = "username";
    private static final String PASS = "password";
    private static final String SQL_QUERY = "SELECT * FROM orders";  // 修改为你的查询SQL
    private static final int BATCH_SIZE = 1;  // 每批次的数量

    private static final String path = "/Users/yuhaodong/Desktop/ouput/xxx.xlsx";

    @Test
    public void test() {
        try {

            int rowNum = 0;  // 行号计数器
            FileInputStream in=null;


            Workbook workbook  = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Orders");  // 创建新的工作表

            for (int l = 0; l < 4; l++) {

                for (int i=0;i<10;i++){
                    System.out.println(rowNum);
                    Row row = sheet.createRow(rowNum++);  // 创建新的行
                    for (int j = 0; j < 3; j++) {  // 遍历每一列的数据
                        Cell cell = row.createCell(j);  // 创建新的单元格
                        Object value = "data"+j;  // 获取数据值
                        if (value instanceof Number) {  // 如果数据是数字类型，则设置为数字单元格
                            cell.setCellValue(((Number) value).doubleValue());
                        } else {  // 其他类型设置为字符串单元格
                            cell.setCellValue(value.toString());
                        }
                    }
                }

                try (FileOutputStream outputStream = new FileOutputStream(path)) {
                    outputStream.flush();
                    workbook.write(outputStream);
                    workbook.close(); //报错 not valid
                    workbook = null;
                    if (in==null){
                        try {
                            in = new FileInputStream(path);
                        } catch (FileNotFoundException e) {
                            e.printStackTrace();
                            return;
                        }
                    }

                    workbook = new XSSFWorkbook(in);  //有in de入参
                    sheet = workbook.getSheet("Orders");  //

                } catch (IOException e) {
                    e.printStackTrace();  // 处理文件写入异常
                }



//                if (rowNum % BATCH_SIZE == 0) {  // 每批次结束时，将工作簿写入文件并清空内存缓存
//                    try (FileOutputStream outputStream = new FileOutputStream("orders.xlsx")) {
//                        workbook.write(outputStream);
//
//                        workbook.clear();  // 清空内存中的工作簿缓存，但不关闭文件流或工作簿对象。
//                    } catch (IOException e) {
//                        e.printStackTrace();  // 处理文件写入异常
//                    }
//                }
            }



        }
        finally {

        }


    }
}
